<?php
namespace app\admin\controller;
use think\Db;

class Stat extends Main
{  
    /**
     * 首页统计
    */
    public function index(){
        $count = Db::name('index_hits')
            ->where('a.id=index_id')
            ->fetchSql(true)
            ->count();
        $data = Db::name('index')
            ->alias('a')
            ->where('type','neq',1)
            ->field("a.*,($count) as hits")
            ->select();
        $this->assign('data',$data);
        return $this->fetch();
    }
    /**
     * 领券记录
     */
    public function exchange(){
    	
    	$where = [];
        $keyword = $this->request->param('keyword');
        if($keyword){
            $where[] = ['e.username|e.mobile|e.cardHyNo|e.cardLevel','like','%'.$keyword.'%'];
        }
    	$begin = $this->request->param('begin');
        $end = $this->request->param('end');
        if($begin || $end){
            $where[] = search_time('gettime',$begin,$end);
        }
    	$data = Db::name('exchange')
            ->alias('e')
            ->where($where)
            ->join('coupon c','e.coupon_id=c.id')
            ->field("e.*,c.coupon_name,c.point,c.promotion_num")
            ->order('id desc')
            ->paginate(15, false, [
            'query' => request()->param()]);
        $this->assign('data',$data);
        $this->assign('keyword',input('keyword'));
        return $this->fetch();
    }

    /**
     * 活动统计
     */
    public function activity(){
    	$where = [];
    	$begin = $this->request->param('begin');
        $end = $this->request->param('end');
        if($begin || $end){
            $where[] = search_time('hittime',$begin,$end);
        }

        $countList = [];
        $actions = Db::name('activity')
        	->order('id desc')
        	->select();
        foreach($actions as $key => $val){
        	//活动点击次数
			$actionHits = Db::name('action_hit')
	            ->alias('h')
	            ->where($where)
	            ->where('action_id = '.$val['id'])
	            ->count();
			//活动点击人数
	        $peopleHits = Db::name('action_hit')
	            ->alias('h')
	            ->where($where)
	            ->where('action_id = '.$val['id'])
	        	->field('count(mobile) as num')
	            ->group('mobile')
	            ->count();
	        $countList[] = [
	        	'title' => $val['title'],
	        	'actionHits' => $actionHits,
	        	'peopleHits' => $peopleHits,
	        ];
        }
//      dump($countList);
        
        //不同活动点击次数
//		$countList = Db::name('action_hit')
//      	->alias('h')
//          ->where($where)
//      	->join('activity a','a.id=h.action_id')
//      	->field('h.*, a.title , count(h.action_id) as num, count(h.mobile) as mobilenum')
//      	->group('h.action_id')
//      	->field('count(h.mobile) as mobilenum')
////      	->select();
//          ->paginate(15, false, [
//          	'query' => request()->param()]);
//		dump($actionHits);
        $this->assign('data',$countList);
        return $this->fetch();
    }
	
	/**
     * 领券统计
     */
    public function coupon(){
    	$where = [];
    	$begin = $this->request->param('begin');
        $end = $this->request->param('end');
        if($begin || $end){
            $where[] = search_time('gettime',$begin,$end);
        }

 
        $countList = [];
        $actions = Db::name('activity')
        	->order('id desc')
        	->select();
        foreach($actions as $key => $val){
	        //活动领券次数
			$couponGets = Db::name('exchange')
	        	->alias('e')
	            ->where($where)
	            ->where('e.action_id = '.$val['id'])
	        	->join('coupon c','c.id=e.coupon_id')
	        	->field('e.* , count(c.action_id) as num')
	            ->count();
	        //活动领券人数
			$couponPeoples = Db::name('exchange')
	        	->alias('e')
	            ->where($where)
	            ->where('e.action_id = '.$val['id'])
	        	->join('coupon c','c.id=e.coupon_id')
	        	->group('e.mobile')
	            ->count();
	        //活动消耗积分总和
			$pointSum = Db::name('exchange')
	        	->alias('e')
	            ->where($where)
	            ->where('e.action_id = '.$val['id'])
	        	->join('coupon c','c.id=e.coupon_id')
	            ->sum("e.point");
	        $pointSum = ceil($pointSum*100)/100;
	        $countList[] = [
	        	'title' => $val['title'],
	        	'couponGets' => $couponGets,
	        	'couponPeoples' => $couponPeoples,
	        	'pointSum' => $pointSum,
	        ];
        }
//      dump($countList);
        //不同活动领券次数
//		$countList = Db::name('exchange')
//      	->alias('e')
//          ->where($where)
//      	->join('coupon c','c.id=e.coupon_id')
//      	->join('activity a','a.id=c.action_id')
//      	->field('e.*, a.title , count(c.action_id) as num')
//      	->group('c.action_id')
//          ->paginate(15, false, [
//          	'query' => request()->param()]);
        $this->assign('data',$countList);
        return $this->fetch();
    }
    
	//进行导出领券记录
    public function downExcelExchange(){
        $actionID = input('actionID');
        $where = [];
        $keyword = $this->request->param('keyword');
        if($keyword){
            $where[] = ['e.username|e.mobile|e.cardHyNo|e.cardLevel','like','%'.$keyword.'%'];
        }
    	$begin = $this->request->param('begin');
        $end = $this->request->param('end');
        if($begin || $end){
            $where[] = search_time('gettime',$begin,$end);
        }
    
        $title = '优惠券领取记录'.date("Ymd");
        $field  = ['id','手机号','姓名','卡号','卡级','券名称','扣减积分','赠券号','兑换时间'];
        
        $record = Db::name('exchange')
        	->alias('e')
        	->join('coupon c','c.id = e.coupon_id')
        	->where($where)
            ->field("e.id,e.mobile,e.userName,e.cardHyNo,e.cardLevel,c.coupon_name,c.point,e.certino,e.gettime")
            ->order('e.id desc')
            ->select();
        dump($record);
        (new Common())->downExcel('券领取记录',$field,$record,$title);
    }
}
